import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentManager {
    private static final String URL = "jdbc:mysql://localhost:3306/student_management?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "";

    // 添加学生信息到数据库
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO students (id, name, gender, clazz, mathScore, javaScore) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGender());
            pstmt.setString(4, student.getClazz());
            pstmt.setDouble(5, student.getMathScore());
            pstmt.setDouble(6, student.getJavaScore());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 根据ID查询学生信息
    public Student queryStudentById(int id) {
        String sql = "SELECT * FROM students WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new Student(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("clazz"),
                        rs.getDouble("mathScore"),
                        rs.getDouble("javaScore")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 显示所有学生信息
    public List<Student> showAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                students.add(new Student(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("clazz"),
                        rs.getDouble("mathScore"),
                        rs.getDouble("javaScore")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 计算学生各科目的平均分数
    public double calculateAverageScore(String subject) {
        String sql = "SELECT AVG(" + subject + ") AS averageScore FROM students";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getDouble("averageScore");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0.0;
    }

    public static void main(String[] args) {
        StudentManager manager = new StudentManager();

        // 添加学生信息示例
        Student newStudent = new Student(1, "张三", "男", "软件工程1班", 85.5, 90.0);
        boolean isAdded = manager.addStudent(newStudent);
        System.out.println("添加学生信息成功: " + isAdded);

        // 查询学生信息示例
        Student queriedStudent = manager.queryStudentById(1);
        System.out.println("查询到的学生信息: " + queriedStudent);

        // 显示所有学生信息示例
        List<Student> allStudents = manager.showAllStudents();
        System.out.println("所有学生信息: " + allStudents);

        // 计算平均分数示例
        double mathAverage = manager.calculateAverageScore("mathScore");
        double javaAverage = manager.calculateAverageScore("javaScore");
        System.out.println("高数平均分: " + mathAverage);
        System.out.println("Java平均分: " + javaAverage);
    }
}